﻿Option Strict On
Option Explicit On
Option Infer On

Imports System
Imports System.Linq
Imports System.Collections.Generic
Imports GenDotNet.TemplateSupport.StandardAttributeExtensions
Imports GenDotNet.Task.Common
Imports GenDotNet.BuildData
Imports <xmlns:kd="http://kadgen.com/Special.xsd">
Imports <xmlns:orm="http://kadgen.com/KADORM.xsd">
Imports System.Xml.Linq
Imports GenDotNet.TemplateSupport.Strings


Public MustInherit Class SqlBase
   Inherits TemplateBase

   Protected MustOverride Function GenerateContents() As String

   Private mPrivileges As IEnumerable(Of IPrivilegeData)
   Private mSPData As IStoredProcedureData

#Region "Overrides"
   Protected Overrides Function SetUp(ByVal baseElement As XElement) As Boolean
      mSPData = SPDataFactory.Load(baseElement)
   End Function

   Protected Overrides Function GenerateFile() As String
      Return Me.GenerateContents()
   End Function

#End Region

   Public ReadOnly Property SPData() As IStoredProcedureData
      Get
         Return Me.mSPData
      End Get
   End Property

   Public ReadOnly Property Privileges() As IEnumerable(Of IPrivilegeData)
      Get
         Return mPrivileges
      End Get
   End Property


   Protected Overridable Function OutputRetrieveSp( _
            ByVal isFirst As Boolean, _
            ByVal recSet As IRecordsetData) _
            As String
      Return _
<code>
   SELECT <%= If(isFirst AndAlso SPData.Distinct, " DISTINCT ", String.Empty) %>
   <%= recSet.Columns.Output() %>
   FROM <%= recSet.OutputTablesAndJoins() %>
   WHERE <%= SPData.Parameters.ORMBuildWhereClause() %>
   ORDER BY <%= recSet.OrderBies.Output %>
</code>.Value
   End Function

   Protected Overridable Function OutputRetrieveSp_SelectFromTemp(ByVal tempTableName As String) As String
      Return <code>
                SELECT * FROM <%= tempTableName %><%= nl %>
             </code>.Value
   End Function

   Protected Overridable Function GetTempTableName(ByVal objectName As String) As String
      Return "#temp" & objectName
   End Function

   Protected Overridable Function OutputExecChildren( _
            ByVal spSet As XElement, _
            ByVal isselectChildren As Boolean, _
            ByVal callingParent As String, _
            ByVal callingParent2 As String) _
            As String
      If String.IsNullOrEmpty(callingParent2) Then
         callingParent2 = "NOMATCH"
      End If
      Dim firstRecSet = spSet...<orm:BuildRecordset>.First()
      Dim position As Int32 = 0
      Dim recordsets = SPData.UniqueRecordsets
      Return <code>
                <%= From rSet In recordsets _
                   Let childSpSet = (From childRSet In spSet.Document...<orm:SPSet> Where childRSet.@Name = rSet.@ChildSPSet).FirstOrDefault _
                   Let childStoredProc = GetChildStoredProcName(spSet, childSpSet) _
                   Select If(rSet.@IsHierarchy = "true", _
                   vbcrlf & "EXEC SelectHiearchy_" & spSet.@Name & " @" & spSet...<orm:RetrieveParameter>.FirstOrDefault.@Name, _
                   If((Not String.IsNullOrEmpty(childStoredProc)) And rSet.@ChildSPSet <> callingParent And rSet.@ChildSPSet <> callingParent2, _
                   vbcrlf & "EXEC " & childStoredProc, String.Empty)) %>
             </code>.Value
   End Function

   Protected Overridable Function GetColumns(ByVal recSet As XElement) As IEnumerable(Of XElement)
      Return From x In recSet...<orm:TableColumn> _
             Where String.IsNullOrEmpty(x.@DependentColumnName) _
             Or x.@IsPrimaryKey = "true" _
             And Not x.@JoinBaseRootKey = "true"

   End Function

   Protected Overridable Function GetChildStoredProcName( _
            ByVal SpSet As XElement, _
            ByVal childspSet As XElement) As String
      If childspSet Is Nothing Then
         Return String.Empty
      End If
      Return "SelectChildren_" & SpSet.@Name & "_" & childspSet.@TableName
   End Function

   Protected Overridable Function ColumnSPListTableName(ByVal item As XElement) As String
      Dim tableName = String.Empty
      Select Case item.Name.LocalName
         Case "SetSelectColumn"
            tableName = item.@TableName
         Case "BuildColumn"
            tableName = item.Ancestors(buildTable).@RightAlias
         Case Else
            tableName = item.@TableAlias
      End Select
      Return tableName
   End Function

   Protected Overridable Function OutputORMMoreJoins(ByVal fKeyPosition As Int32) As String
      ' Only used for Retrieve children
      Return String.Empty
   End Function

   Protected Overridable Function OutputRetrieveSP_WhereClause(ByVal whereClause As String) As String
      If String.IsNullOrEmpty(whereClause) Then
         Return " WHERE " & whereClause
      End If
      Return String.Empty
   End Function

   Protected Overridable Function GetTableName(ByVal recset As XElement) As String
      Return (From table In recset...<orm:BuildTable> Take 1 Select table.@OriginalName).FirstOrDefault
   End Function

   Protected Overridable Function GetBuildJoin(ByVal recSet As XElement) As String
      Return _
   <code>
      <%= From ormJoin In recSet...<orm:Join> _
         Let rightTableDeclaration = "[" & ormJoin.@RightOriginalName & "]" & If(String.IsNullOrEmpty(ormJoin.@RightAlias), String.Empty, " As " & ormJoin.@RightAlias) _
         Let rightTable = GetBuildJoinRightTable(ormJoin) _
         Let leftTable = GetBuildJoinLeftTable(ormJoin) _
         Select _
         <code>[<%= leftTable %>] <%= ormJoin.@Type %> JOIN <%= rightTableDeclaration %>
                  ON <%= From joinKey In ormJoin...<orm:JoinKey> _
                        Select _
                        <code>
                           <%= "" %>[<%= leftTable %>].[<%= joinKey.@LeftColumn %>] = [<%= rightTable %>].[<%= joinKey.@RightColumn %>]
               </code> %>
         </code>.Value %>
   </code>.Value
   End Function

   Protected Overridable Function GetBuildJoinLeftTable(ByVal join As XElement) As String
      Return join.@LeftOriginalName
   End Function

   Protected Overridable Function GetBuildJoinRightTable(ByVal join As XElement) As String
      Return If(String.IsNullOrEmpty(join.@RightAlias), join.@RightOriginalName, join.@RightAlias)
   End Function

   Protected Overridable Function GetFromClauseTable( _
            ByVal tableName As String, _
            ByVal tableAlias As String) _
            As String
      If tableName = tableAlias Then
         Return "[" & tableName & "]"
      Else
         Return "[" & tableName & "] As " & tableAlias
      End If
   End Function

   Protected Overrides Function GetFileName(ByVal element As System.Xml.Linq.XElement) As String

   End Function

   Public Overrides ReadOnly Property Id() As System.Guid
      Get

      End Get
   End Property
End Class

Public Module ExtensionMethods

   <System.Runtime.CompilerServices.Extension()> _
   Public Function OutputOpening(ByVal sPData As IStoredProcedureData, ByVal templateName As String) As String
      Return <code>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[<%= sPData.Name %>]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[<%= sPData.Name %>]
GO

/* This stored proc was generated, please do not change */
/* Entity:        <%= sPData.EntityName %> */
/* Template Name: <%= templateName %>
/* Stored Proc:   <%= sPData.Name %> */

CREATE PROCEDURE <%= sPData.Name %>
             </code>.Value
   End Function

   <System.Runtime.CompilerServices.Extension()> _
   Public Function OutputClose(ByVal sPData As IStoredProcedureData) As String
      Return <code>
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

<%= From privilege In SPData.Privileges Select <code>
GRANT EXECUTE ON <%= SPData.Name %> TO [<%= privilege.Grantee %>] </code>.Value %>
             </code>.Value
   End Function

   <System.Runtime.CompilerServices.Extension()> _
   Public Function OutputParameterDeclarations(ByVal params As IEnumerable(Of IParameterData)) As String
      Dim last = params(params.Count - 1)
      Return (From param In params Select _
       <code>
          <%= nws %>@<%= param.Name & " " & param.SQLType %>
          <%= If(((param.MaxLength > 0) And (param.MaxLength < Int32.MaxValue)), _
             "(" & param.MaxLength & ")", String.Empty) %>
          <%= If(param.IsAutoIncrement, " OUTPUT ", String.Empty) %>
          <%= If(param.AllowNulls, " = null ", String.Empty) %>
       </code>).ConcatenateWithComma()
   End Function

   <System.Runtime.CompilerServices.Extension()> _
   Public Function ORMBuildWhereClause( _
         ByVal params As IEnumerable(Of IParameterData)) _
         As String
      Return (From param In params Select _
                   <code>@<%= param.Name %> = [<%= param.TableName %>].[<%= param.ColumnName %>]
                   </code>).ConcatenateWith(andString)
   End Function

   <System.Runtime.CompilerServices.Extension()> _
   Public Function Output(ByVal columns As IEnumerable(Of IColumnData)) As String
      Return (From column In columns _
              Select _
              <code>
                 [<%= column.TableName %>].[<%= column.Name %>]<%= nws %>
                 <%= If(column.Name.Equals(column.alias, StringComparison.InvariantCultureIgnoreCase), String.Empty, " As " & column.alias) %>
              </code>).ConcatenateWithComma()
   End Function

   <System.Runtime.CompilerServices.Extension()> _
   Public Function OutputTablesAndJoins(ByVal recSet As IRecordsetData) As String
      Return _
<code> [<%= recSet.Tables(0) %>] 
   <%= From item In recSet.Joins Select _
      <code>
         <%= item.JoinType.Output %> JOIN [<%= item.RightTable %>] <%= nws %>
         <%= If(item.RightTable.Equals(item.RightAlias, StringComparison.InvariantCultureIgnoreCase), String.Empty, " As " & item.RightAlias) %>
            ON <%= From joinKey In item.JoinKeys Select _
                  <code>
                     [<%= item.LeftAlias %>].[<%= joinKey.LeftColumn %>] = [<%= item.RightAlias %>].[<%= joinKey.RightColumn %>]
                  </code> %>
      </code> %>
</code>.Value

   End Function

   <System.Runtime.CompilerServices.Extension()> _
   Public Function Output(ByVal orderBies As IEnumerable(Of IOrderByData)) As String
      Return ( From orderBy In orderBies Select _
      <code>
         <%= orderBy.Name %><%= space %><%= orderBy.Direction %>
      </code>).ConcatenateWithComma() 
   End Function


End Module
